<?php


JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_puser/tables');
JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_jobs/tables');

/**
 * Resumes controller
 */
class PResumesModelResumes extends JModelLegacy
{
   public function getList($limitstart=-1, $limit=0, $terms=array()) {
      $dir = JRequest::getString('dir', null);
      $jobid = JRequest::getInt('jobid', 0); 
      $status_id = JRequest::getInt('status_id', 0);
      $time = JRequest::getString('time', null);
      $resumeids= JRequest::getString('resumeids',null);
      //import resumes uri
      $resumesdir=JRequest::getString('resumesdir',null);
      //get delaytask uri by foldername
      $foldername = JRequest::getString('foldername', null);
      if($dir == null && $foldername !== null) {
         $dir = $this->getFolderIdByName($foldername);
      }

      if(!$dir) $dir = 'all';
      $all = ($dir === 'all');
      $saved = ($dir === 'saved'); 
      $trashed = ($dir === 'trashed'); 
      $shared = ($dir === 'shared');
      $shared = $all && PCompanyHelper::isJobManagerViewOnly() ? true : $shared;
      $company = ($dir === 'company');
      $company = PCompanyHelper::isJobManager() && !empty($jobid) ? true: $company;
      
      // if company folder is true, then shared must be false 
      $shared = $company ? false: $shared; 

      $keyword = JRequest::getString('keyword', null);
      $tag = JRequest::getString('tag', null);
      $isnew = JRequest::getInt('isnew', null);
      $matchresult = JRequest::getInt('matchresult', 0);
      $workexp = JRequest::getInt('workexp', 0);
      $degree = JRequest::getString('degree', null);
      $agefrom = 0;
      $ageto = JRequest::getInt('age', 0);
      $sex = JRequest::getString('sex', null);
      $source = JRequest::getString('source',null);
      $id_state = 0;
      $id_city = 0;

      $startdate = JRequest::getString('startdate', null);
      $enddate = JRequest::getString('enddate', null);

      // Do offsite term search
      if (!empty($terms)) {
         // $keyword = isset($terms['keyword']) ? $terms['keyword'] : null; // For future use
         $workexp = isset($terms['workexp']) ? $terms['workexp'] : 0;
         if (isset($terms['sex'])) {
            $sex = $terms['sex'] == 1 ? 'M' : ($terms['sex'] == 2 ? 'F' : null);
         }
         $agefrom = isset($terms['agefrom']) ? intval($terms['agefrom']) : 0;
         $ageto = isset($terms['ageto']) ? intval($terms['ageto']) : 0;
         $time = isset($terms['update_time']) ? $terms['update_time'].'w' : '';
         $id_state = isset($terms['id_state']) ? intval($terms['id_state']) : 0;
         $id_city = isset($terms['id_city']) ? intval($terms['id_city']) : 0;
         $degree = isset($terms['degree_level']) ? $terms['degree_level'] : null;
      }

      // no pagination ?
      $nopagination = JRequest::getInt('nopagination', 0); 
     
      list($listorders, $delaysort) = PResumesHelper::getListOrder();
      $ordersSql = implode(", ", $listorders);

      $user = PFactory::getMasterUser(); 
      $db = PFactory::getDbo();
      
      $joinSql = '';
      $searchSql = '';
      if(PCompanyHelper::isJobManager() && !PFactory::isMasterUser() && (($all && !$company) || $trashed)) {
         $subuser = PCompanyHelper::getSubUser();
         if(!empty($subuser) && !empty($subuser->assignedjobs)) {
            $searchSql .= ' AND j.id IN (' . implode(',', $subuser->assignedjobs) . ') ';
         } else {
            $searchSql .= ' AND j.id = -1';
         }
      }
      
      if (is_numeric($dir) && $dir > 0) {
         $joinSql .= " LEFT JOIN *PREFIX*hr_resume_folder AS rfd ON (rfd.folder_id=" . intval($dir) . " AND rfd.owner=" . $db->quote(OC_User::getUser()) . " ) 
                       LEFT JOIN *PREFIX*hr_resume_folder_files AS rffd ON (rffd.folder_id=rfd.folder_id AND rffd.jobseeker_id=s.id) ";
         $searchSql .= " AND rffd.folder_id IS NOT NULL";
      }
      
      if ($jobid > 0) {
         $searchSql .= " AND j.id = " . intval($jobid);
      }
      if ($status_id > 0) {
         $searchSql .= " AND s.status_id = " . intval($status_id);
      }
      if($time) {
         list($starttime, $endtime) = PResumesHelper::getTimeRange($time);
         $startdate = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
         $enddate = PFactory::getDate($endtime)->format('Y-m-d H:i:s');
         $searchSql .= " AND s.date_added >= ".$db->quote($startdate) ." AND s.date_added < ".$db->quote($enddate);
      }

      if ($id_city>0) {
         $searchSql .= " AND s.id_city = ".intval($id_city)." ";
      } elseif ($id_state>0) {
         $searchSql .= " AND s.id_province = " . intval($id_state)." ";
      }

      $keyword = trim($keyword);
      if (!empty($keyword)) {
         $searchSql .= " AND (s.name LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR s.phone LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR s.email LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR s.id IN (SELECT DISTINCT jobseeker_id FROM *PREFIX*hr_jobseeker_tag_term_details AS d
                         LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS t ON t.id=d.tagid WHERE t.verified=1 AND d.owner=e.user_id
                         AND t.name LIKE " . $db->quote('%' . $keyword. '%') . "))";
      }
      $tag = trim($tag);
      if (!empty($tag)) {
         $searchSql .= " AND (s.id IN (SELECT DISTINCT jobseeker_id FROM *PREFIX*hr_jobseeker_tag_term_details AS d
                         LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS t ON t.id=d.tagid WHERE t.verified=1 AND d.owner=e.user_id
                         AND t.name LIKE " . $db->quote('%' . $tag . '%') . "))";
      }
      if (isset($isnew)) {
         $searchSql .= " AND s.is_new = " . intval($isnew);
      }
      if ($matchresult > 0) {
         $searchSql .= " AND s.match_degree >= " . intval($matchresult);
      }
      if ($workexp > 0) {
         $searchSql .= " AND s.workexp_months >= " . intval($workexp)*12;
      }
      if (!empty($degree)) {
         $degreeLevelRange = PResumesHelper::getDegreeLevelRange($degree);
         
         if (!empty($degreeLevelRange)) {
            foreach ($degreeLevelRange as $key => $degreeLevel) {
               $degreeLevel = $db->quote($degreeLevel);
               if($key == 'min')
                  $searchSql .= " AND degree_table.id >= (SELECT id FROM *PREFIX*hr_degree_level WHERE degree_level=" . $degreeLevel . ")"; 
               elseif($key == 'max')
                  $searchSql .= " AND degree_table.id < (SELECT id FROM *PREFIX*hr_degree_level WHERE degree_level=" . $degreeLevel . ")"; 
               elseif($key == 'maxi')
                  $searchSql .= " AND degree_table.id <= (SELECT id FROM *PREFIX*hr_degree_level WHERE degree_level=" . $degreeLevel . ")"; 
            }
         }
      }
      if ($agefrom+$ageto > 0) {
         $searchSql .= " AND (year(curdate()) - year(s.dateofbirth)) >= ".intval($agefrom)." AND (year(curdate()) - year(s.dateofbirth)) <= " . intval($ageto);
      }
      if (!empty($sex)) {
         $searchSql .= " AND s.sex = " . $db->quote($sex);
      }
      if(!empty($source)){
         $searchSql .= " AND LOCATE(".$db->quote($source).",s.source)>0";
      }
      $masteruser = PFactory::isMasterUser();
      if(!$shared || $masteruser) {
         $query = "SELECT SQL_CALC_FOUND_ROWS s.*, j.job_title, j.id as jobid, j.system as jobsystem, s.online_interview_progress, hjs.title AS status_title, j.online_interview_requirements, " . 
                  " (SELECT GROUP_CONCAT(hjtt.name) FROM *PREFIX*hr_jobseeker_tag_term_details AS hjttd 
                       LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS hjtt ON hjtt.id=hjttd.tagid
                       WHERE hjtt.verified=1 AND hjttd.jobseeker_id=s.id AND hjttd.owner=" . $user->id . ") AS tags, " .
                  ($masteruser ? \OCP\PERMISSION_ALL . " as permissions, " : "IF(jm.jobid IS NULL, " . \OCP\PERMISSION_READ . ", " . \OCP\PERMISSION_ALL . ") as permissions, ") .
                  " IF(rff.folder_id IS NULL, 0, 1) AS is_saved, s.school_level " .
                  " FROM *PREFIX*hr_jobseeker AS s " . 
                  " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " . 
                  " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id " . 
                  " LEFT JOIN *PREFIX*hr_jobseeker_status AS hjs ON hjs.id=s.status_id " .
                  " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                  ($masteruser ? "" : " LEFT JOIN *PREFIX*hr_jobmanager AS jm ON (jm.jobid=s.job_id AND jm.manager=" . $db->quote(OC_User::getUser()) . ") ") .
                  " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                    LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=s.id) " . $joinSql .
                  " WHERE e.user_id = " . $user->id . " AND j.is_trashed=0 " .
                  ($saved > 0 ? " AND rff.folder_id IS NOT NULL" : "") . 
                  ($trashed > 0 ? " AND s.is_trashed = 1" : " AND s.is_trashed=0") . $searchSql .
                  " ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC "; 

         # having email in order by to make order by with limit consistent. when mysql optimizes limit, it 
         # uses some indexes 
      }
      else {
         $query = "SELECT SQL_CALC_FOUND_ROWS a.permissions, s.*, j.job_title, j.id as jobid, j.system as jobsystem, s.online_interview_progress, hjs.title AS status_title, j.online_interview_requirements, " . 
                  " (SELECT GROUP_CONCAT(hjtt.name) FROM *PREFIX*hr_jobseeker_tag_term_details AS hjttd 
                       LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS hjtt ON hjtt.id=hjttd.tagid
                       WHERE hjtt.verified=1 AND hjttd.jobseeker_id=s.id AND hjttd.owner=" . $user->id . ") AS tags, " .
                  " IF(rff.folder_id IS NULL, 0, 1) AS is_saved, s.school_level" .
                  " FROM `*PREFIX*share` AS a  " . 
                  " LEFT JOIN *PREFIX*hr_jobseeker AS s ON s.id=a.item_source" .
                  " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " .
                  " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id " .
                  " LEFT JOIN *PREFIX*hr_jobseeker_status AS hjs ON hjs.id=s.status_id " .
                  " LEFT JOIN *PREFIX*groups_custom AS g ON g.groupname=a.share_with " .
                  " LEFT JOIN *PREFIX*group_user_custom AS gu ON gu.gid=g.gid" .
                  " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                  " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                    LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=s.id) " .
                  " WHERE (a.share_with = " . $db->quote(OC_User::getUser()) . 
                  "       OR (gu.uid=" . $db->quote(OC_User::getUser()) . " AND a.share_type=" . OCP\Share::SHARE_TYPE_GROUP . "))" . 
                  " AND a.item_type='resumes' " .
                  " AND e.user_id = " . $user->id . $searchSql . " AND j.is_trashed=0 " .
                  " ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC "; 

         // look from share list 
      }
      $limitstart = $limitstart >=0  ? $limitstart : JRequest::getVar('limitstart', 0, '', 'int');

     // Get the pagination request variables
      $app = PFactory::getApplication();
      $limit = $limit > 0 ? $limit : $app->getUserStateFromRequest('resumes.limit', 'limit', 50, 'int');
      $limit = $limit > 100 ? 100 : $limit;

      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ( $limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);

      
      /* try to load from cache */
      $cache = CacheHelper::getCache('app_resumes'); 
      // get cached data now 
      list($result, $total) = $cache->call(array($this, '_getList'), $query, intval($limitstart), intval($limit));
      
      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      if($result) {
         $tables = array('#__jobs_job', '*PREFIX*hr_jobseeker', '*PREFIX*hr_jobseeker_status', '*PREFIX*hr_resume_folder_files');
         if(!$shared || $masteruser) 
            array_push($tables, '*PREFIX*hr_jobmanager');
         else 
            array_push($tables, '*PREFIX*share');
         CacheHelper::setTriggers(OC_User::getUser(), $cache, $tables);
         // if one user's operation has impact on other user view, it's for entire company 
         if(!PFactory::isMasterUser()) 
            CacheHelper::setTriggers($user->id, $cache, $tables);
      }
      else {
         // Don't cache null data
         $cache->removeCache(array($this, '_getList'), $query);
      }

      // generate html output now 
      $pagination = new PPagination( $total, $limitstart, $limit);
      $jobtitle = ''; 
      if($jobid > 0 && count($result) > 0) {
         $jobtitle = $result[0]->job_title; 
      }
      
      /* save query history so we know where we are */
      $app = PFactory::getApplication();
      $token = shortHash($query, 5);
      $resumedir = $app->getUserState('resumedir', null);
      $searchtokens = $app->getUserState('searchtokens', null);
      /*
       * searchtokens key explain:
       * count: how many resumes are opened
       */
      $resumedir = $resumedir ? $resumedir : array();
      $searchtokens = $searchtokens ? $searchtokens : array();
      $searchtokens[$token] = empty($searchtokens[$token]) ? array('dir'=>$dir, 'count'=>0) : $searchtokens[$token];
      $searchtokens[$token]['dir'] = $dir;
      
      if (!empty($joinSql)) {
         $searchtokens[$token]['join'] = $joinSql;
      }
      
      if (!empty($searchSql)) {
         $searchtokens[$token]['search'] = $searchSql;
      }
      
      if (!empty($ordersSql)) {
         $searchtokens[$token]['orders'] = $ordersSql;
      }
      
      if ($jobid>0 || $status_id>0 || !empty($keyword) || !empty($tag) || isset($isnew) || $matchresult>0 || $workexp>0 || !empty($degree) || $agefrom+$ageto > 0 || !empty($sex) || !empty($time) || !empty($source)) {
         $resumedir['searching'] = 1;
      } else {
         $resumedir['searching'] = 0;
      }
      
      $app->setUserState('resumedir', $resumedir);
      $app->setUserState('searchtokens', $searchtokens);

      // limit delaysort to a very small list to avoid performance issue 
      if(!empty($delaysort) && count($result) <= 10) {
         PResumesHelper::sortList($result, $delaysort);
      }
      
      return array($result, $pagination, $token, $jobid, $jobtitle);
   }

   public function _getList($query, $limitstart=0, $limit=0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit); 
      $result = $db->loadObjectList();

      $query = 'SELECT FOUND_ROWS()'; 
      $db->setQuery($db->useLastConnection($query));
      $total = $db->loadResult();
      return array($result, $total);
   }

   /**
    * Get resume folder id based on given name. This only works for folders owned by master user. 
    * @param  string $resumesdir folder name 
    * @return array
    */
   public function getFolderIdByName($resumesdir){
      if(empty($resumesdir))
         return null;
      $db = PFactory::getDbo();
      if(PFactory::isMasterUser()){
         $user = PFactory::getMasterUser();
         $uid = $user->id;
      }else{
         $uid = OC_User::getUser();
      }
      // join delaytask to see if the fid is auto-created subfolder name based on task 
      $query = "SELECT f.folder_id, d.summary " . 
               " FROM *PREFIX*hr_resume_folder AS f " . 
               " LEFT JOIN #__delaytasks AS d ON CONCAT('_t', d.id) = f.folder_name" . 
               " WHERE f.folder_name =".$db->quote($resumesdir) . " AND f.owner= ".$db->quote($uid); 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   public function getFolderNameById($folderid){
      if(empty($folderid))
         return null;
      $db = PFactory::getDbo();
      if(PFactory::isMasterUser()){
         $user = PFactory::getMasterUser();
         $uid = $user->id;
      }else{
         $uid = OC_User::getUser();
      }
      $query = "SELECT f.folder_name, d.id as delaytaskid " . 
               " FROM *PREFIX*hr_resume_folder AS f " . 
               " LEFT JOIN #__delaytasks AS d ON CONCAT('_t', d.id) = f.folder_name" . 
               " WHERE f.folder_id =".intval($folderid). " AND f.owner= ".$db->quote($uid); 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }
}
